package com.jackrain.nea.omslocal.Dao;

import com.alibaba.dubbo.common.utils.NetUtils;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.parser.Feature;
import com.alibaba.fastjson.util.TypeUtils;
import com.jackrain.nea.config.PropertiesConf;
import com.jackrain.nea.omslocal.service.LocalLogService;
import com.jackrain.nea.omslocal.service.SendMqServies;
import com.jackrain.nea.omslocal.util.JsonKeyCheck;
import com.jackrain.nea.omslocal.util.RegexDateUtil;
import com.jackrain.nea.util.ApplicationContextHandle;
import com.jackrain.nea.util.Tools;
import com.jackrain.nea.util.Validator;
import com.jackrain.nea.web.query.QueryEngine;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.time.FastDateFormat;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import java.io.PrintWriter;
import java.io.StringWriter;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.util.*;

/**
 * Description: 保存淘宝平台商品数据
 * Created with IntelliJ IDEA.
 * User: cs
 * Date: 2017/3/31
 * Time: 17:22
 */
@Component
@Slf4j
public class TaoBaoItemDao {

    @Autowired
    private RegexDateUtil regexDate;
    @Autowired
    private LocalLogService localLogService;
    private FastDateFormat fastformat = FastDateFormat.getInstance("yyyy-MM-dd HH:mm:ss");

    @Autowired
    private SendMqServies sendMq;

    /**
     * 保存淘宝平台商品的总方法
     *
     * @param taobaoorderArr
     * @param topic
     */
    public void itemStart(JSONArray taobaoorderArr, String msgkey, String topic) throws Exception {
        TaoBaoItemDao taoBaoItemDao = ApplicationContextHandle.getBean(TaoBaoItemDao.class);
        PropertiesConf pconf = (PropertiesConf) ApplicationContextHandle.getBean("propertiesConf");
        // 定义接收消费日志信息的 jsonobject
        JSONObject infoObj = new JSONObject();
        List tids = new ArrayList();
        int countFaild = 0;
        //  需要解析订单结构,分离数据
        for (Object obj : taobaoorderArr) {
            // 定义接收商品明细表的 JSONArray itemsArr
            JSONArray itemsArr = null;
            JSONObject tempObj = null;
            Long num_iid = 0L;
            try {
                // 循环遍历拿到单条商品数据
                JSONObject tempJson = JSONObject.parseObject(obj.toString(), Feature.OrderedField);
                tempObj = tempJson.getJSONObject("jdp_response").getJSONObject("item_get_response").getJSONObject("item");
                //tempObj = tempJson.getJSONObject("item_get_response").getJSONObject("item");
                if (tempObj.getJSONObject("skus") != null) {
                    // 商品明细表数据  skus sku
                    itemsArr = tempObj.getJSONObject("skus").getJSONArray("sku");

                } else {
                    log.debug("子表数据为空");
                }

                num_iid = tempObj.getLong("num_iid");
                // 订单表主表数据
                tempObj.remove("skus");
                tempObj.remove("item_imgs");
                tempObj.remove("prop_imgs");
                // 根据 num_iid 查询数据库,判断该数据是 新增 或是 修改
                String selectSql = "SELECT id FROM T_IESTAOBAOITEM WHERE num_iid = ?";
                int id = Tools.getInt(QueryEngine.getInstance().doQueryOne(selectSql, new Object[]{num_iid}), -1);
                if (id < 0) {
                    // 数据不为空 保存数据
                    taoBaoItemDao.insert(tempObj, itemsArr, topic);
                    // 保存数据到本地日志表
                    localLogService.insertLogDeatail(tempObj, num_iid, 1, topic, msgkey);
                } else {
                    // 根据查询到的 id 更新主表数据
                    taoBaoItemDao.update(tempObj, itemsArr, id, topic);
                    // 保存数据到本地日志表
                    localLogService.insertLogDeatail(tempObj, num_iid, 2, topic, msgkey);
                }

                tids.add(num_iid);

            } catch (Exception e) {
                // 获取 流
                StringWriter sw = new StringWriter();
                PrintWriter pw = new PrintWriter(sw);
                // 逐条将错误信息以消息发送到线上发送到线上
                JSONObject errObj = new JSONObject();
                errObj.put("bus_id", num_iid);
                errObj.put("tablename", pconf.getProperty("ons.tborder"));
                errObj.put("topic", topic);
                errObj.put("tag", pconf.getProperty("ons.comp"));
                // 线下消费报错的主机 ip
                errObj.put("client_ip", NetUtils.getLocalAddress());
                // 将错误以流写出
                e.printStackTrace(pw);
                errObj.put("errmsg", sw.toString());
                errObj.put("protime", System.currentTimeMillis());
                errObj.put("code", -1);
                sendMq.sendMqinfoObj(errObj, pconf.getProperty("ons.loginfor.err.topic"), pconf.getProperty("ons.loginfor.err.tag"));

                countFaild++;
                //log.error(e.getMessage() + topic + "===============>" + num_iid);
                localLogService.insertLog(e.getMessage(), tempObj, num_iid, topic, msgkey);
            }
            // 调用更新线上日志的服务
            infoObj.put("msgkey", msgkey);
            infoObj.put("topic", topic);
            infoObj.put("countsuccess", (taobaoorderArr.size() - countFaild));
            infoObj.put("protime", System.currentTimeMillis());
            infoObj.put("client_ip", NetUtils.getLocalAddress());
            infoObj.put("tids", tids);
            infoObj.put("code", 0);
            infoObj.put("total", taobaoorderArr.size());

            // 消费完成,将消费的消息信息发送至线上,更新线上日志表
            // 参数 1 消息体  2 消息 topic  3 消息 tag
            String msgId = sendMq.sendMqinfoObj(infoObj, pconf.getProperty("ons.loginfor.suf.topic"), pconf.getProperty("ons.loginfor.suf.tag"));
            if("-1".equals(msgId)){
                for(int i = 0; i < 3; i++){
                    if(!"-1".equals(msgId)){
                        break;
                    }
                    //重试回执
                    msgId = sendMq.sendMqinfoObj(infoObj, pconf.getProperty("ons.loginfor.suf.topic"), pconf.getProperty("ons.loginfor.suf.tag"));
                }
            }
            log.debug("taobaoitem消费完成回执 msgId= {}, topic= {}, tag= {}", msgId, pconf.getProperty("ons.loginfor.suf.topic"), pconf.getProperty("ons.loginfor.suf.tag"));
        }
    }

    @Transactional
    public void insert(JSONObject tempObj, JSONArray ordersArr, String topic) throws Exception {
        Object outId = null;
        // 新增数据
        // 数据不为空 保存数据
        if (!tempObj.isEmpty()) {
            Map map = null;
            map = saveItem(tempObj);
            outId = map.get("seq");
        } else {
            log.debug("主表数据为空");
        }
        if (ordersArr != null) {
            log.debug("drders 订单商品明细");
            saveItemSkus(ordersArr, "T_IESTAOBAOITEMSKU", outId);
        } else {
            log.debug("drders 订单商品明细表为空");
        }

        // 在执行完之后需要更改主表的 istrans 字段为 0
        String sql = "update T_IESTAOBAOITEM SET istrans = 0 WHERE id = ?";
        QueryEngine.getInstance().executeUpdate(sql, new Object[]{outId});


    }

    @Transactional
    public void update(JSONObject tempObj, JSONArray ordersArr, int id, String topic) throws Exception {


        if (tempObj != null) {
            // 根据查询到的 id 更新主表数据
            this.updateItem(tempObj, id);
        } else {
            log.error("商品主表数据为空");
        }
        if (ordersArr != null) {
            // 更新子表 订单商品明细表数据
            this.updateItemSkus(ordersArr, "T_IESTAOBAOITEMSKU", "sku_id", id);
        } else {
            log.error("商品子表数据为空");
        }

        // 更新操作之后需要更新 istrans 为0
        String sql = "update T_IESTAOBAOITEM SET istrans = 0 WHERE id = ?";
        QueryEngine.getInstance().executeUpdate(sql, new Object[]{id});


    }


    /**
     * 保存商品主表
     *
     * @param jsonObj
     * @return
     * @throws SQLException
     */

    public Map saveItem(JSONObject jsonObj) throws SQLException, ParseException {

        JsonKeyCheck checkjson = (JsonKeyCheck) ApplicationContextHandle.getBean(JsonKeyCheck.class);
        jsonObj = checkjson.checkJsonkeycache(jsonObj, "T_IESTAOBAOITEM", checkjson.getInsertcolsmap());
        Map map = new HashMap();
        // 将固定需要维护的字段加入 jsonObj,  creatTime, modified , outstatus
        // 获取主表的 sequences
        String squnceSql = "select get_wsequences('T_IESTAOBAOITEM') as SEQ from dual";
        Long id = TypeUtils.castToLong(QueryEngine.getInstance().doQueryOne(squnceSql));
        jsonObj.put("id", id);
        String columnName = "";
        String columnValue = "";
        Set set = jsonObj.keySet();
        Object[] objArr = jsonObj.values().toArray();

        for (Object obj1 : set) {
            columnName = columnName + "," + obj1;
        }
        columnName = columnName.substring(1);

        for (int i = 0; i < objArr.length; i++) {

            if (objArr[i] instanceof String) {
                if (regexDate.regexDate(((String) objArr[i]))) {
                    Date date = fastformat.parse(((String) objArr[i]));
                    objArr[i] = date;
                }
            }
            columnValue = columnValue + ",?";
        }

        columnValue = columnValue.substring(1);

        String sql = "insert into T_IESTAOBAOITEM (" + columnName + ") values (" + columnValue + ")";

        int code = QueryEngine.getInstance().executeUpdate(sql, objArr);

        map.put("code", code);
        map.put("seq", id);
        return map;
    }

    /**
     * 保存商品子表
     *
     * @param dataArr
     * @return
     * @throws SQLException
     */

    public void saveItemSkus(JSONArray dataArr, String tableName, Object outObj) throws SQLException, ParseException {

        for (Object obj : dataArr) {
            JSONObject jsonObj = JSONObject.parseObject(obj.toString(), Feature.OrderedField);

            JsonKeyCheck checkjson = ApplicationContextHandle.getBean(JsonKeyCheck.class);
            jsonObj = checkjson.checkJsonkeycache(jsonObj, "T_IESTAOBAOITEMSKU", checkjson.getInsertcolsmap());
            // 将固定需要维护的字段加入 jsonObj,  creatTime, modified , outstatus
            // outstatus 默认值为 1 ,当主表所有明细更新完成,将此状态更改为 2 ;

            // 获取子表的 sequences
            String squnceSql = "select get_wsequences('" + tableName + "') as SEQ from dual";
            int id = TypeUtils.castToInt(QueryEngine.getInstance().doQueryOne(squnceSql));

            jsonObj.put("id", id);
            jsonObj.put("iestaobaoitemid", outObj);
            String columnName = "";
            String columnValue = "";
            Set set = jsonObj.keySet();
            Object[] objArr = jsonObj.values().toArray();

            for (Object obj1 : set) {
                columnName = columnName + "," + obj1;
            }
            columnName = columnName.substring(1);

            for (int i = 0; i < objArr.length; i++) {

                if (objArr[i] instanceof String) {
                    if (regexDate.regexDate(((String) objArr[i]))) {
                        Date date2 = fastformat.parse(((String) objArr[i]));
                        objArr[i] = date2;
                    }
                }
                columnValue = columnValue + ",?";
            }
            columnValue = columnValue.substring(1);

            String sql = "insert into " + tableName + " (" + columnName + ") values (" + columnValue + ")";

            int code = QueryEngine.getInstance().executeUpdate(sql, objArr);

        }

    }


    /**
     * 更新订单主表
     *
     * @param jsonObj
     * @return
     * @throws SQLException
     */

    public Map updateItem(JSONObject jsonObj, int id) throws SQLException, ParseException {

        JsonKeyCheck checkjson = ApplicationContextHandle.getBean(JsonKeyCheck.class);

        jsonObj = checkjson.checkJsonkeycache(jsonObj, "T_IESTAOBAOITEM", checkjson.getUpdatecolsmap());

        Map map = new HashMap();
        String params = "";
        Set set = jsonObj.keySet();
        Object[] objArr = jsonObj.values().toArray();
        for (Object obj1 : set) {
            Object tempobject = jsonObj.get(obj1);
            params = params + "" + obj1 + " = ?,";
        }

        for (int i = 0; i < objArr.length; i++) {

            if (Validator.isNull(objArr[i])) {
                objArr[i] = "null";
            }
            if (objArr[i] instanceof String) {
                if (regexDate.regexDate(((String) objArr[i]))) {
                    Date date2 = fastformat.parse(((String) objArr[i]));
                    objArr[i] = date2;
                }
            }
        }
        log.debug("更新商品主表: id = " + id);
        params = params.substring(0, params.length() - 1);
        String sql = "update T_IESTAOBAOITEM set " + params + " where id = ?";
        int code = QueryEngine.getInstance().executeUpdate(sql, ArrayUtils.add(objArr, id));
        return map;
    }

    /**
     * 更新订单子表
     *
     * @param dataArr
     * @param parid   关联主表数据的 id 值
     * @return
     * @throws SQLException
     */

    public int updateItemSkus(JSONArray dataArr, String tableName, String outkey, int parid) throws SQLException, ParseException {

        log.debug("更新商品子表 parid : " + parid);
        changeSkuStatus(parid);

        for (Object obj : dataArr) {
            JSONObject jsonObj = JSONObject.parseObject(obj.toString(), Feature.OrderedField);
            JsonKeyCheck checkjson = (JsonKeyCheck) ApplicationContextHandle.getBean(JsonKeyCheck.class);

            jsonObj = checkjson.checkJsonkeycache(jsonObj, tableName, checkjson.getInsertcolsmap());
            // 根据唯一外键查询判断 该数据是否存在,存在就更新数据  不存在新增子表数据,
            String selectSql = "select id from " + tableName + " where " + outkey + " = ?";
            Object objId = QueryEngine.getInstance().doQueryOne(selectSql, new Object[]{jsonObj.get(outkey)});
            int id = Tools.getInt(QueryEngine.getInstance().doQueryOne(selectSql, new Object[]{jsonObj.get(outkey)}), -1);
            if (id < 0) {
                // 新增关联明细数据
                reSaveDetal(jsonObj, parid);
                log.debug("明细数据不存在,已新增 parid : " + parid);
                continue;
            }


            // 将固定需要维护的字段加入 jsonObj
            String params = "";
            Set set = jsonObj.keySet();
            Object[] objArr = jsonObj.values().toArray();
            for (Object obj1 : set) {
                Object tempobject = jsonObj.get(obj1);
                params = params + "" + obj1 + " = ?,";

            }
            params = params.substring(0, params.length() - 1);

            for (int i = 0; i < objArr.length; i++) {

                if (objArr[i] instanceof String) {
                    if (regexDate.regexDate(((String) objArr[i]))) {
                        Date date2 = fastformat.parse(((String) objArr[i]));
                        objArr[i] = date2;
                    }
                }
            }
            String sql = "update " + tableName + " set " + params +",delete_flag = 0"+ " where id = ? ";
            int code = QueryEngine.getInstance().executeUpdate(sql, ArrayUtils.add(objArr, id));
        }

        return dataArr.size();
    }


    /**
     * 保存商品子表,商品表
     *
     * @return
     * @throws SQLException
     */

    public synchronized void reSaveDetal(JSONObject jsonObj, Object outObj) throws SQLException, ParseException {

        String tableName = "T_IESTAOBAOITEMSKU";
        JsonKeyCheck checkjson = (JsonKeyCheck) ApplicationContextHandle.getBean(JsonKeyCheck.class);
        jsonObj = checkjson.checkJsonkeycache(jsonObj, tableName, checkjson.getInsertcolsmap());
        log.debug("no detail save ===>" + jsonObj.toString());
        // 获取子表的 sequences
        String squnceSql = "select get_wsequences('" + tableName + "') as SEQ from dual";
        int id = TypeUtils.castToInt(QueryEngine.getInstance().doQueryOne(squnceSql));
        // 将固定需要维护的字段加入 jsonObj,  creatTime, modified , outstatus
        jsonObj.put("id", id);
        jsonObj.put("iestaobaoitemid", outObj);
        String columnName = "";
        String columnValue = "";
        Set set = jsonObj.keySet();
        Object[] objArr = jsonObj.values().toArray();

        for (Object obj1 : set) {
            columnName = columnName + "," + obj1;
        }
        columnName = columnName.substring(1);

        for (int i = 0; i < objArr.length; i++) {

            if (objArr[i] instanceof String) {
                if (regexDate.regexDate(((String) objArr[i]))) {
                    Date date2 = fastformat.parse(((String) objArr[i]));
                    objArr[i] = date2;
                }
            }
            columnValue = columnValue + ",?";
        }
        columnValue = columnValue.substring(1);

        String sql = "insert into " + tableName + " (" + columnName + ") values (" + columnValue + ")";

        int code = QueryEngine.getInstance().executeUpdate(sql, objArr);


    }

    /**
     * 更改子表数据状态
     * @return
     * @throws SQLException
     */
    public synchronized void changeSkuStatus(int id) throws SQLException, ParseException {

        String sql = "update T_IESTAOBAOITEMSKU set delete_flag = 1 where iestaobaoitemid = ? ";
        int code = QueryEngine.getInstance().executeUpdate(sql, new Object[]{id});

    }

}
